package guo.jdbcpart;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

import guo.dbutil.JdbcTemplate;
import guo.dbutil.ResultSetHandler;
import guo.domain.User;
import guo.domain.User2List;
public class UserTableOperationForJdbcTemplate {
	public List<User> quaryAll() {
		String sql = "select * from users";
		List<User> list = JdbcTemplate.query(sql, (rs) -> {
			List<User> list0 = new ArrayList<>();
			try {
				while (rs.next()) {
				}
				User user = new User(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4),
						LocalDateTime.now());
				list0.add(user);
			} catch (SQLException e) {

				e.printStackTrace();

			}
			return list0;
		}, new Object[] {});
		return list;
	}

	public List<User> quaryAll1() {
		String sql = "select * from users";
		List<User> list = JdbcTemplate.query(sql, new ResultSetHandler<List<User>>() {
			public List<User> handler(ResultSet rs) {
				List<User> list0 = new ArrayList<>();
				try {
					while (rs.next()) {
						User user = new User(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4),LocalDateTime.now());
						list0.add(user);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				return list0;
			}
		}, new Object[] {});
		return list;
	}

	public List<User> queryAll2() {
		String sql = "select * from users";
		List<User> list = JdbcTemplate.query(sql, new User2List(), new Object[] {});
		return list;
	}

	public User queryById(int id) {
		String sql = "select * from users where id=?";
		Connection connection = DBConnection.getConnection();
		User user = null;
		try {
			PreparedStatement psmt = connection.prepareStatement(sql);
			psmt.setInt(1, id);
			ResultSet rs = psmt.executeQuery();
			if (rs.next()) {
				user = new User(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), LocalDateTime.now());
			}
		} catch (SQLException e) {
			System.out.println(
					UserTableOperation.class.getName() + ": query By Id method error,message is " + e.getMessage());
		}
		return user;
	}

	public int addUser(User user) {
		String sql = "insert into users(id,name,password,sex) values(users_id.nextval,?,?,?)";
		return JdbcTemplate.update(sql, user.getName(), user.getPassword(), user.getSex());
	}

}
